/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package conn.dao;

import conn.bean.BEmpleado;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author kuma-chin
 */
public class DEmpleado extends DBase {

    public List getEmpleados() {
        List lista = new ArrayList();
        BEmpleado empleados = null;
        String query = null;
        Statement stmt = null;
        ResultSet res = null;
        this.init_mysql();  // se inicia la conección a la base de datos

        try {
            stmt = conn.createStatement();

            res=stmt.executeQuery(""
                    + "SELECT idEmpleado,nombre,apellidoPaterno,apellidoMaterno, username, password, email FROM empleados ");
            while (res.next()){
                BEmpleado bempleado=new BEmpleado(res.getInt(1),res.getString(2),res.getString(3),res.getString(4),res.getString(5),res.getString(6),res.getString(7));
                lista.add(bempleado);
            }

            while (res.next()) {
            empleados = new BEmpleado(res.getInt(1), res.getString(2),res.getString(3),res.getString(4),res.getString(5),res.getString(6),res.getString(7));
            }

            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            System.out.println("SqlException : " + ex.toString());
        } catch (Exception ex) {
            System.out.println("Exception : " + ex.toString());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    ;
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    ;
                }
                conn = null;
            }
        }
        return lista;
    }

    public BEmpleado getEmpleado(int id) {
        BEmpleado empleado = null;
        String query = null;
        Statement stmt = null;
        ResultSet res = null;
        this.init_mysql();  // se inicia la conección a la base de datos

        try {
            stmt = conn.createStatement();

            res = stmt.executeQuery(""
                    + "SELECT idEmpleado, nombre, apellidoPaterno,"
                    + " apellidoMaterno, username, password, email"
                    + " FROM empleados"
                    + " WHERE idEmpleado=" + id
                    + "");
            if (res.next()){
                empleado = new BEmpleado(res.getInt(1), res.getString(2), res.getString(3), res.getString(4), res.getString(5), res.getString(6), res.getString(7));
            }
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            System.out.println("SqlException : " + ex.toString());
        } catch (Exception ex) {
            System.out.println("Exception : " + ex.toString());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    ;
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    ;
                }
                conn = null;
            }
        }
        return empleado;
    }

    public void crearEmpleado(BEmpleado bempleado) {
        Statement stmt = null;
        ResultSet res = null;

        this.init_mysql();  // se inicia la conección a la base de datos

        try {

            stmt = conn.createStatement();
            stmt.executeUpdate(""
                    + " INSERT INTO empleados "
                    + " (nombre, apellidoPaterno, apellidoMaterno, password, username, email )"
                    + " VALUES "
                    + " ('" + bempleado.getNombre() + "','" + bempleado.getApellidop() + "','"
                    + bempleado.getApellidom() + "','" + bempleado.getUsername() + "','" + bempleado.getPassword()
                    + "','" + bempleado.getEmail() + "')");

            stmt.close();
            conn.close();

        } catch (SQLException ex) {
            System.out.println("SqlException : " + ex.toString());
        } catch (Exception ex) {
            System.out.println("Exception : " + ex.toString());
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException e) {
                    ;
                }
                res = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    ;
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    ;
                }
                conn = null;
            }
        }
    }

    public int getUltimoId() {
        Statement stmt = null;
        ResultSet res = null;

        this.init_mysql();  // se inicia la conección a la base de datos
        int id = 0;
        try {

            stmt = conn.createStatement();
            res = stmt.executeQuery("SELECT MAX(idEmpleado) from empleados");

            if (res.next()) {

                id = res.getInt(1);
            }

            res.close();
            stmt.close();
            conn.close();

        } catch (SQLException ex) {
            System.out.println("SqlException : " + ex.toString());
        } catch (Exception ex) {
            System.out.println("Exception : " + ex.toString());
        } finally {
            if (res != null) {
                try {
                    res.close();
                } catch (SQLException e) {
                    ;
                }
                res = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    ;
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    ;
                }
                conn = null;
            }
        }
        return id;
    }

    public void updateEmpleado( BEmpleado bempleado){
        String query = null;
        Statement stmt = null;
        this.init_mysql();  // se inicia la conección a la base de datos

        try {

            stmt = conn.createStatement();
            query = "UPDATE empleados SET nombre = '" + bempleado.getNombre() + "', "+
                            "apellidoPaterno = '" + bempleado.getApellidop() + "', "+
                            "apellidoMaterno = '" + bempleado.getApellidom() + "', "+
                            "username = '" + bempleado.getUsername() + "', "+
                            "password = '" + bempleado.getPassword() + "', "+
                            "email = '" + bempleado.getEmail() + "' "+
                           " WHERE idEmpleado =" + bempleado.getIdEmpleado();
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
        }catch(SQLException ex){
           System.out.println("SqlException : "+ex.toString());
        }catch(Exception ex){
           System.out.println("Exception : "+ex.toString());
        }finally {
            if (stmt != null) {
              try { stmt.close(); } catch (SQLException e) { ; }
              stmt = null;
            }
            if (conn != null) {
              try { conn.close(); } catch (SQLException e) { ; }
              conn = null;
            }
        }
    }

    public void borrarempleado( int idempleado){
        String query = null;
        Statement stmt = null;
        this.init_mysql();  // se inicia la conección a la base de datos

        try {

            stmt = conn.createStatement();
            query = "DELETE from empleados where idEmpleado = '" + idempleado + "'";
            stmt.executeUpdate(query);
            query = "UPDATE campana set idEmpleado = '6' where idEmpleado = '" + idempleado + "'";
            stmt.executeUpdate(query);

            stmt.close();
            conn.close();
        }catch(SQLException ex){
           System.out.println("SqlException : "+ex.toString());
        }catch(Exception ex){
           System.out.println("Exception : "+ex.toString());
        }finally {
            if (stmt != null) {
              try { stmt.close(); } catch (SQLException e) { ; }
              stmt = null;
            }
            if (conn != null) {
              try { conn.close(); } catch (SQLException e) { ; }
              conn = null;
            }
        }
    }

    public void borrarModificaciones( int idempleado){
        String query = null;
        Statement stmt = null;
        this.init_mysql();  // se inicia la conección a la base de datos

        try {

            stmt = conn.createStatement();
            query = "SELECT idSesion from sesion where idEmpleado = '" + idempleado + "';";
            stmt.executeUpdate(query);
            stmt.close();
            conn.close();
        }catch(SQLException ex){
           System.out.println("SqlException : "+ex.toString());
        }catch(Exception ex){
           System.out.println("Exception : "+ex.toString());
        }finally {
            if (stmt != null) {
              try { stmt.close(); } catch (SQLException e) { ; }
              stmt = null;
            }
            if (conn != null) {
              try { conn.close(); } catch (SQLException e) { ; }
              conn = null;
            }
        }
    }

}
